﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Diagnostics;
using DBWTProject.models;
using DBWTProject.route;
using System.Globalization;
using System.Data;

namespace DBWTProject.persistence
{
    public static class RouteDAO
    {
        private static SqlConnection _sqlConnection;
        public static void init()
        {
            if (_sqlConnection == null || _sqlConnection.State == ConnectionState.Closed)
            {
                _sqlConnection = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=\"|DataDirectory|\\Database1.mdf\";Integrated Security=True;User Instance=True");
                _sqlConnection.Open();
                Debug.WriteLine("ServerVersion: {0}", _sqlConnection.ServerVersion);
                Debug.WriteLine("State: {0}", _sqlConnection.State);
            }
        }

        public static void close()
        {
            _sqlConnection.Close();
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="user"></param>
        /// <param name="pw"></param>
        /// <returns>null, wenn nicht gefunden</returns>
        public static User GetUser(string username, string pw)
        {
            string queryString = String.Format("SELECT name, fullname, pw, id FROM users WHERE name='{0}' and pw='{1}'", username, pw);//Ich scheiß auf SQL-Injection.
            SqlCommand myCommand = new SqlCommand(queryString, _sqlConnection);
            using (SqlDataReader myReader = myCommand.ExecuteReader())
            {
                while (myReader.Read())
                {
                    User user = new User()
                    {
                        Name = myReader["name"].ToString(),
                        FullName = myReader["fullname"].ToString(),
                        Password = myReader["pw"].ToString(),
                        ID = Int32.Parse(myReader["id"].ToString()),
                    };
                    return user;
                }
            }
            return null;
        }

        /// <summary>
        /// Updatet den User in der db. Welcher Datensatz, entscheidet id. Welche Daten geschrieben werden, entscheidet inhalt der User-Objekts.
        /// </summary>
        /// <param name="id"></param>
        /// <param name="user"></param>
        public static void UpdateUser(int id, User user)
        {
            string queryString = String.Format("UPDATE users SET name='{0}', fullname='{1}', pw='{2}'  WHERE id='{3}'", user.Name, user.FullName, user.Password, id);//Ich scheiß auf SQL-Injection.
            SqlCommand myCommand = new SqlCommand(queryString, _sqlConnection);
            int result = myCommand.ExecuteNonQuery();
        }

        /// <summary>
        /// Fügt einen neuen Routeneintrag in die DB ein.
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="kml"></param>
        public static void InsertNewKML(User user, string routeName, string kml)
        {
            KmlParser parser = new KmlParser(kml);
            CultureInfo culturInfoUS = new CultureInfo("en-US", false);
            string queryString = String.Format("INSERT INTO kml_routes(userid, raw_kml, name, date) VALUES ('{0}','{1}','{2}','{3}')", user.ID, kml, routeName, parser.GetRouteStartDate().ToString("yyyy-MM-ddThh:mm:ss"));
            SqlCommand myCommand = new SqlCommand(queryString, _sqlConnection);
            int result = myCommand.ExecuteNonQuery();
        }

        /// <summary>
        /// Holt alle Routen zur userid
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        internal static IList<Route> GetRoutes(User user)
        {
            return GetRoutes(user, null, null, null);
        }

        /// <summary>
        /// Holt alle Routen eines User in einem bestimmten Zeitraum und mit namefilter (contains-Bedingung für Name-Field)
        /// Parameter mit null werden ignoriert
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="dateStart">wenn null -> ignoriert</param>
        /// <param name="dateEnd">wenn null -> ignoriert</param>
        /// <param name="namefilter">wenn null -> ignoriert</param>
        /// <returns></returns>
        public static IList<Route> GetRoutes(User user, DateTime? dateStart, DateTime? dateEnd, string namefilter)
        {
            init();
            string queryString = String.Format("SELECT id, userid, raw_kml, name, date FROM kml_routes WHERE userid='{0}' ", user.ID);
            if (dateStart != null && dateEnd != null)
            {
                queryString += String.Format("AND date BETWEEN convert(date,'{0}') and convert(date,'{1}')", dateStart.Value.AddDays(-1).Date.ToString("MM/dd/yyyy"), dateEnd.Value.AddDays(1).Date.ToString("MM/dd/yyyy"));
                //AddDays(+/-1) -> da BETWEEN die Begrenzungswerte NICHT mit einschließt, was wir hier aber wollen. Daher wird ein Tag auf addiert bzw. abgezogen

                //queryString += String.Format("AND date BETWEEN convert(datetime,'{0}') and convert(datetime,'{1}')", dateStart.Value.Date.ToString("dd.MM.yyyy"), dateEnd.Value.Date.ToString("dd.MM.yyyy"));
                //queryString += String.Format("AND CAST(date AS date) BETWEEN CAST('{0}' AS date) and CAST('{1}' AS date) ", dateStart.Value.Date.ToString("dd.MM.yyyy"), dateEnd.Value.Date.ToString("dd.MM.yyyy"));
                //queryString += String.Format("AND CAST(date AS date) BETWEEN CAST('{0}' AS date) and CAST('{1}' AS date) ", dateStart, dateEnd);
            }

            if (namefilter != null)
            {
                queryString += String.Format("AND name like '%{0}%' ", namefilter);
            }
            SqlCommand myCommand = new SqlCommand(queryString, _sqlConnection);
            IList<Route> resultList = new List<Route>();
            using (SqlDataReader myReader = myCommand.ExecuteReader())
            {
                while (myReader.Read())
                {
                    Route route = new Route()
                    {
                        ID = Int32.Parse(myReader["id"].ToString()),
                        User = user,
                        Date = (DateTime)myReader["date"],
                        Name = myReader["name"].ToString(),
                        RawKML = myReader["raw_kml"].ToString()
                    };

                    resultList.Add(route);
                }
            }
            return resultList;
        }

        /// <summary>
        /// Holt die Routen mit den übergebenen IDs.
        /// Damit es nur _ein_ DB-Aufruf ist und nicht mehrere getRoute(routeId)-Aufrufe
        /// </summary>
        /// <param name="compareRouteIDsSplit"></param>
        /// <returns></returns>
        public static IList<Route> GetRoutes(string[] routeIDs)
        {
            string queryString = "SELECT id, userid, raw_kml, name, date FROM kml_routes WHERE ";
            foreach (string routeId in routeIDs)
            {
                queryString += "id = '" + routeId + "' OR ";
            }
            queryString = queryString.Substring(0, queryString.Length - 4);//letztes OR wieder entfernen

            SqlCommand myCommand = new SqlCommand(queryString, _sqlConnection);
            IList<Route> resultList = new List<Route>();
            using (SqlDataReader myReader = myCommand.ExecuteReader())
            {
                while (myReader.Read())
                {
                    Route route = new Route()
                    {
                        ID = Int32.Parse(myReader["id"].ToString()),
                        //UserID = Int32.Parse(myReader["userid"].ToString()),
                        User = null,
                        Date = (DateTime)myReader["date"],
                        Name = myReader["name"].ToString(),
                        RawKML = myReader["raw_kml"].ToString(),
                    };
                    resultList.Add(route);
                }
            }
            return resultList;
        }

        /// <summary>
        /// Holt genau eine Route
        /// Parameter mit null werden ignoriert
        /// UserID wird nicht beachtet, man könnte sich also von jedem Nutzer die Routen anzeigen lassen
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public static Route GetRoute(int routeId)
        {
            string queryString = String.Format("SELECT id, userid, raw_kml, name, date FROM kml_routes WHERE id='{0}' ", routeId);

            SqlCommand myCommand = new SqlCommand(queryString, _sqlConnection);

            using (SqlDataReader myReader = myCommand.ExecuteReader())
            {
                myReader.Read();

                Route route = new Route()
                {
                    ID = Int32.Parse(myReader["id"].ToString()),
                    //UserID = Int32.Parse(myReader["userid"].ToString()),
                    User = null, //XXX
                    Date = (DateTime)myReader["date"],
                    Name = myReader["name"].ToString(),
                    RawKML = myReader["raw_kml"].ToString(),
                };

                return route;
            }
        }

        /// <summary>
        /// Löscht die Routen mit den in der Liste enthaltenen IDs.
        /// </summary>
        /// <param name="routeIdDeathList"></param>
        public static void DeleteRoutes(IList<int> routeIdDeathList)
        {
            string queryString = "DELETE FROM kml_routes WHERE ";
            foreach (int id in routeIdDeathList)
            {
                queryString += "id = '" + id + "' OR ";
            }
            queryString = queryString.Substring(0, queryString.Length - 4);//letztes OR wieder entfernen
            SqlCommand myCommand = new SqlCommand(queryString, _sqlConnection);
            int result = myCommand.ExecuteNonQuery();
        }

        /// <summary>
        /// Liefert eine Lister der Monate zurück, die Routen enthalten. z.B. (2,9), wenn es Routen im Februar und September gibt.
        /// </summary>
        /// <param name="year"></param>
        /// <returns></returns>
        public static IList<int> GetMonthsWithAvailableRoutes(int year, User user)
        {
            SqlCommand myCommand = new SqlCommand(String.Format("SELECT DISTINCT MONTH(date) AS month, YEAR(date) AS year FROM kml_routes WHERE YEAR(date) = '{0}' AND userid = '{1}'", year, user.ID), _sqlConnection);
            IList<int> list = new List<int>();
            using (SqlDataReader myReader = myCommand.ExecuteReader())
            {
                while (myReader.Read())
                {
                    list.Add(Int32.Parse(myReader["month"].ToString()));
                }
            }
            return list;
        }

        /// <summary>
        /// return Tuple<int>, damit ich mit der Liste schön DataBinding machen kann.
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public static IList<Tuple<int>> GetYearsWithRoutes(User user)
        {
            SqlCommand myCommand = new SqlCommand("SELECT DISTINCT YEAR(date) AS year FROM kml_routes WHERE userid='" + user.ID + "'", _sqlConnection);
            IList<Tuple<int>> list = new List<Tuple<int>>();
            using (SqlDataReader myReader = myCommand.ExecuteReader())
            {
                while (myReader.Read())
                {
                    list.Add(new Tuple<int>(Int32.Parse(myReader["year"].ToString())));
                }
            }
            return list;
        }
    }
}